In [5]:
%load_ext sql
%sql sqlite://
Out[5]:
In [6]:
# Create tables & insert some random numbers
# Note: in Postgresql, try the generate_series function...
%sql DROP TABLE IF EXISTS R; DROP TABLE IF EXISTS S; DROP TABLE IF EXISTS T;
%sql CREATE TABLE R (A int); CREATE TABLE S (A int); CREATE TABLE T (A int);
for i in range(1,6):
%sql INSERT INTO R VALUES (:i)
for i in range(1,10,2):
%sql INSERT INTO S VALUES (:i)
for i in range(1,11,3):
%sql INSERT INTO T VALUES (:i)
In [7]:
%%sql
drop table if exists product; -- This needs to be dropped if exists, see why further down!
drop table if exists company;
pragma foreign_keys = ON; -- WARNING by default off in sqlite
create table company (
cname varchar primary key, -- company name uniquely identifies the company.
stockprice money, -- stock price is in money
country varchar); -- country is just a string
insert into company values ('ToyWorks', 25.0, 'USA');
insert into company values ('ToyFriends', 65.0, 'China');
insert into company values ('ToyCo', 15.0, 'China');
create table product(
pname varchar, -- name of the product
price money, -- price of the product
category varchar, -- category
manufacturer varchar, -- manufacturer
primary key (pname, manufacturer),
foreign key (manufacturer) references company(cname));
insert into product values('Pikachu', 19.99, 'Toy', 'ToyWorks');
insert into product values('Pikachu', 19.99, 'Toy', 'ToyFriends');
insert into product values('Pokeball', 29.99, 'Electronic', 'ToyCo');
insert into product values('Bulbasaur', 149.99, 'Toy', 'ToyFriends');
insert into product values('Charizard', 203.99, 'Toy', 'ToyCo');
insert into product values('PokeCamera', 19.99, 'Electronic', 'ToyWorks');
Out[7]:
In [ ]:
Теперь протестируйте ваш запрос для случая, где $S = \emptyset$- Что произошло и почему?
Выполните запрос снизу, и еще раз выполните верхний запрос
In [4]:
%%sql
delete from S;
Out[4]:
In [ ]:
In [ ]: